﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace PUCIT.eFCS.DataAccess
{
    public class UserEntries
    {


        //Business.BusinessClass obj = new Business.BusinessClass(); isn't accesible why not??
        
         public bool UserExistsWindows(String uName)
         {
             if (GetUserIdByUsername(uName) > 0)
             {
                 return true;
             }
             return false;
         } 
        
         
        public bool UserExists(String email)
        {
            if (GetUserId(email) > 0)
            {
                return true;
            }
            return false;
        }
        
        public int GetUserIdByUsername(String uName)
        {
            int uId = -1;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "select userId from [users] where username=@uName";
            SqlParameter p1 = new SqlParameter("@uName", uName);
            com.Parameters.Add(p1);
            uId = Convert.ToInt32(com.ExecuteScalar());
            con.Close();
            return uId;

        }

        public String GetUserNameByUserId(int uId)
        {
            String uName = null;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "select username from [users] where userId=@uId";
            SqlParameter p1 = new SqlParameter("@uId", uId);
            com.Parameters.Add(p1);
            uName = com.ExecuteScalar().ToString();
            con.Close();
            return uName;

        }
        
        public int GetUserId(String email)
        {
            int uId = -1;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "select userId from [users] where email = @email";
            SqlParameter p1 = new SqlParameter("@email", email);
            com.Parameters.Add(p1);
            uId = Convert.ToInt32(com.ExecuteScalar());
            con.Close();
            return uId; 
            
        }
        public List<string> SelectAllCategoryNames()
        {
            string categoryname;
            List<string> categoryNames = new List<string>();
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();

            try
            {
                SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
                con1.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = con1;
                com.CommandType = CommandType.Text;
                com.CommandText = "select * from [usertype]";

                SqlDataReader reader = com.ExecuteReader();

                // Call Read before accessing data.
                while (reader.Read())
                {
                    categoryname = (string)reader[1];
                    categoryNames.Add(categoryname);
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                con.Close();
            }
            return categoryNames;
        }
        public int GetCategoryIdByCategoryName(string categoryName)
        {
            int categoryId  = 0;
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();

            try
            {
                SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
                con1.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = con1;
                com.CommandType = CommandType.Text;
                com.CommandText = "select * from [Category] where categoryName = '" + categoryName + "'";

                SqlDataReader reader = com.ExecuteReader();

                // Call Read before accessing data.
                while (reader.Read())
                {
                    categoryId = (int)reader[0];
                }
            }
            catch (Exception e)
            {

            }
            finally
            {
                con.Close();
            }
            return categoryId;
        }
        public void InsertSectionUserRow(int sectionId, int categoryId, string category)
		{
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();

            try
            {
                SqlConnection con1 = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
                con1.Open();
                SqlCommand com = new SqlCommand();
                com.Connection = con1;
                com.CommandType = CommandType.Text;
                com.CommandText = "Insert into [secuser] (secid, usertypeid, category) values (" + sectionId + ", " + categoryId + ", '" + category + "')";
                
                com.ExecuteNonQuery();

            }
            catch (Exception e)
            {

            }
            finally
            {
                con.Close();
            }
        }
        public void AddUsers(String name, String email, int categoryId)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();
                
            try
            {
                SqlCommand com = new SqlCommand();
                com.Connection = con;
                com.CommandType = CommandType.StoredProcedure;
            //    email = "empty";
                com.CommandText = "uspInsertUsersTableRow";
                SqlParameter p1 = new SqlParameter("@vusername", name);
                SqlParameter p2 = new SqlParameter("@vemail", email);
                SqlParameter p3 = new SqlParameter("@vusertypeId", categoryId);

                com.Parameters.Add(p1);
                com.Parameters.Add(p2);
                com.Parameters.Add(p3);
                com.ExecuteNonQuery();
            }
            catch (Exception e)
            {

            }
            finally
            {
                con.Close();
            }
            
        }

        public int GetCategoryId(string degree)
        {
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "select usertypeId from [usertype] where title=@degreeParam";
            SqlParameter p1 = new SqlParameter("@degreeParam", degree);
            com.Parameters.Add(p1);
            int cId = Convert.ToInt32(com.ExecuteScalar());
            con.Close();
            return cId;
            
        }

        public String GetCategoryName(int uId)
        {
            string username = "";
            SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["eFCSConnectionString"].ConnectionString);
            con.Open();
            SqlCommand com = new SqlCommand();
            com.Connection = con;
            com.CommandType = CommandType.Text;
            com.CommandText = "select * from [usertype] where usertypeId= "+ uId;
            
            SqlDataReader dr = com.ExecuteReader();

            while (dr.Read())
            {
                username = dr[1].ToString();
            }

            return username;
        }
    }
}
